﻿using System;
using System.Text;
using System.Data;
using CRMFramework;
using System.Text.RegularExpressions;
using System.Globalization;
using CRMEntity;
using System.Collections.Generic;

namespace CRMBiz
{
    public static class ApplicationContactHandler
    {
        public static DataSet GetApplicationContactList(int pageIndex, int pageSize, string searchtext, string conditiontext, string sortingText, string days)
        {
            DataSet ds;
            try
            {

                string strdays = string.Empty;
                if (days == "30")
                    strdays = " AND DATEDIFF(now(), EditDate) < 30";
                else if (days == "90")
                    strdays = " AND DATEDIFF(now(), EditDate) < 90";
                else
                    strdays = "";

                conditiontext = conditiontext.Replace("%_", "%");

                string strRemoveDuplicate = " AND (DupwithContact IS NULL OR DupwithContact='')";
                string strDefaultSort = " Order By  ContactID Desc";
                string query = " SELECT ContactID,CONCAT(trim(FirstName),' ',trim(MiddleName),' ',trim(SurName)) as ClientName,DateBirth,State,udf_getformatedDate(CreateDate) as CreateDate ,udf_getformatedDate(EditDate) AS EditDate,(select count(*) from crm_contact Where deletedFlag=0 " + strRemoveDuplicate;
                if (!string.IsNullOrEmpty(searchtext))
                {
                    String[] Words = searchtext.Split(' ');
                    if (Words.Length == 2)
                    {
                        var sbWhere = new StringBuilder(" AND ")
                            .AppendFormat("(FirstName like '{0}%'", Words[0])
                            .AppendFormat(" AND SurName like '{0}%')", Words[1])
                            .Append(conditiontext);
                        var sbSql = new StringBuilder(query);
                        sbSql.Append(sbWhere + strdays);
                        sbSql.AppendFormat(") as Total from crm_contact WHERE deletedFlag=0 {0}", strRemoveDuplicate);
                        sbSql.Append(sbWhere + strdays);
                        if (string.IsNullOrEmpty(sortingText))
                            sbSql.Append(strDefaultSort);
                        else
                            sbSql.AppendFormat(" order by {0}", sortingText);

                        sbSql.Append(" LIMIT ").Append((pageIndex - 1) * pageSize).Append(", ").Append(pageSize);
                        ds = DBhelper.ExecuteDataset(sbSql.ToString());
                    }
                    else if (Words.Length == 3 && !IsNumberOnly(searchtext))
                    {
                        var sbWhere = new StringBuilder(" AND ")
                           .AppendFormat("(FirstName like '{0}%'", Words[0])
                           .AppendFormat(" AND MiddleName like '{0}%'", Words[1])
                           .AppendFormat(" AND SurName like '{0}%')", Words[2])
                           .Append(conditiontext);
                        var sbSql = new StringBuilder(query);
                        sbSql.Append(sbWhere + strdays);
                        sbSql.AppendFormat(") as Total from crm_contact WHERE deletedFlag=0 {0}", strRemoveDuplicate);
                        sbSql.Append(sbWhere + strdays);
                        if (string.IsNullOrEmpty(sortingText))
                            sbSql.Append(strDefaultSort);
                        else
                            sbSql.AppendFormat(" order by ", sortingText);

                        sbSql.Append(" LIMIT ").Append((pageIndex - 1) * pageSize).Append(", ").Append(pageSize);
                        ds = DBhelper.ExecuteDataset(sbSql.ToString());
                    }
                    else
                    {
                        var sbWhere = new StringBuilder(" AND ");
                        if (IsEmailOnly(searchtext))
                            sbWhere.AppendFormat("(Email like '{0}%' OR secondEmailAddress like '{0}%' )", searchtext);
                        else if (IsNumberOnly(searchtext))
                        {
                            searchtext = searchtext.Replace(" ", "");
                            string strmob = string.Empty;

                            int len = searchtext.Length;

                            if (searchtext.Contains(" "))
                                sbWhere.AppendFormat("(Mobile like '{0}%')", searchtext);
                            else
                            {
                                if (len == 10)
                                    strmob = searchtext.Substring(0, 4) + " " + searchtext.Substring(4, 3) + " " + searchtext.Substring(7, 3);
                                else if (len > 4 && len <= 7)
                                    strmob = searchtext.Substring(0, 4) + " " + searchtext.Substring(4, len - 4);
                                else if (len > 7 && len < 10)
                                    strmob = searchtext.Substring(0, 4) + " " + searchtext.Substring(4, len - 4) + " " + searchtext.Substring(7, len - 7);
                                else if (len <= 4 || len > 10)
                                    strmob = searchtext;

                                sbWhere.AppendFormat(" (Mobile like '{0}%')", strmob);
                            }
                        }
                        else
                            sbWhere.AppendFormat("(SurName like '{0}%')", searchtext);
                        sbWhere.Append(conditiontext);
                        var sbSql = new StringBuilder(query);
                        sbSql.Append(sbWhere + strdays);

                        sbSql.AppendFormat(") as Total from crm_contact WHERE deletedFlag=0 {0}", strRemoveDuplicate);
                        sbSql.Append(sbWhere + strdays);
                        if (string.IsNullOrEmpty(sortingText))
                            sbSql.Append(strDefaultSort);
                        else
                            sbSql.AppendFormat(" order by {0}", sortingText);
                        sbSql.Append(" LIMIT ").Append((pageIndex - 1) * pageSize).Append(", ").Append(pageSize);
                        ds = DBhelper.ExecuteDataset(sbSql.ToString());
                    }
                }
                else
                {
                    if (!string.IsNullOrEmpty(conditiontext))
                    {
                        var sbSql = new StringBuilder(query)
                        .Append(conditiontext + strdays)
                        .AppendFormat(" ) as Total from crm_contact WHERE deletedFlag=0 {0}", strRemoveDuplicate)
                        .Append(conditiontext + strdays);

                        if (string.IsNullOrEmpty(sortingText))
                            sbSql.Append(strDefaultSort);
                        else
                            sbSql.AppendFormat(" Order By  {0}", sortingText);
                        sbSql.Append(" LIMIT ").Append((pageIndex - 1) * pageSize).Append(", ").Append(pageSize);
                        ds = DBhelper.ExecuteDataset(sbSql.ToString());
                    }
                    else
                    {
                        var sbSql = new StringBuilder(query + strdays)
                        .AppendFormat(" ) as Total from crm_contact WHERE deletedFlag=0 {0}{1}", strRemoveDuplicate, strdays);
                        if (string.IsNullOrEmpty(sortingText))
                            sbSql.Append(strDefaultSort);
                        else
                            sbSql.AppendFormat(" order by {0}", sortingText);

                        sbSql.Append(" LIMIT ").Append((pageIndex - 1) * pageSize).Append(", ").Append(pageSize);
                        ds = DBhelper.ExecuteDataset(sbSql.ToString());
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        public static DataSet GetApplicationContact(int ContactID, string OperatorId)
        {
            DataSet ds;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@ContactID", ContactID),
                    DBhelper.SetParameter("@OperatorID", OperatorId),
                    DBhelper.SetParameter("@LatestTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")),
                };

                string sbSql = "insert into crm_Recent(ContactID,OperatorID,LatestTime) values(@ContactID,@OperatorID,@LatestTime); selECT *,IFNULL(DupwithContact,0) AS dupwith, udf_getformatedDate(createDate) as cdate FROM crm_contact where contactid=@ContactID;";
                ds = DBhelper.ExecuteDataset(sbSql, para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }
        public static DataSet GetAppLoanRecord(int ContactID, string sortText, int noInApp)
        {
            DataSet ds;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@ContactID", ContactID),
                    DBhelper.SetParameter("@sortText", sortText),
                };

                StringBuilder query = new StringBuilder("SELECT  a.PurposeSummary, a.app_id,udf_getformatedDate(a.CreatedDate) AS cdate,a.CreatedDate AS 'age',udf_getformatedDate(a.EditDate) AS EditDate, a.CreatedDate as cdate1, a.EditDate as EditDate1,")
                             .Append(" CAST(a.DesiredAmount AS DECIMAL) AS DesiredAmount, CAST(a.OfferedAmount AS DECIMAL) AS OfferedAmount ,a.app_result,a.comesfrom, ")
                             .Append(" (select contactid  FROM crm_contact  WHERE ContactID=@ContactID) AS ContactId, ")
                             .Append(" (SELECT CONCAT(FirstName,' ',MiddleName,' ',SurName) AS ClientName FROM crm_contact WHERE ContactID=@ContactID)AS ClientName")
                             .Append(" FROM crm_application a INNER JOIN ( SELECT ac.app_id FROM crm_applicationcontact ac INNER JOIN crm_contact c ON c.ContactID= ac.ContactID AND ac.deletedFlag=0")
                             .Append(" WHERE (c.ContactID=@ContactID  OR  c.DupwithContact=@ContactID)) tblAC ON tblAC.app_id=a.app_id AND a.deletedFlag=0 ORDER BY @sortText");

                ds = DBhelper.ExecuteDataset(query.ToString(), para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }
        /// <summary>
        /// Get application info by contact name -- from Wade 20140212
        /// </summary>
        /// <param name="app_id"></param>
        /// <param name="firstname"></param>
        /// <param name="middlename"></param>
        /// <param name="surname"></param>
        /// <returns></returns>
        public static DataSet GetAppLoanRecordByName(int app_id, string firstname,string middlename,string surname)
        {
            DataSet ds=new DataSet();
            DataTable dt = new DataTable();
            List<int> contactidList = GetContactIdListByName(app_id, firstname, middlename, surname);

            for (int i = 0; i < contactidList.Count; i++)
            {
                dt = ApplicationContactHandler.GetAppLoanRecord(contactidList[i], "", 1).Tables[0];

                if (i == 0)
                {
                    ds.Tables.Add(dt.Copy());
                }
                else
                {
                    foreach (DataRow r in dt.Rows)
                    {
                        ds.Tables[0].Rows.Add(r.ItemArray);
                    }
                }
            }
            return ds;
        }
        /// <summary>
        /// Get contact id by Contact name --from Wade 20140212
        /// </summary>
        /// <param name="app_id"></param>
        /// <param name="firstname"></param>
        /// <param name="middlename"></param>
        /// <param name="surname"></param>
        /// <returns></returns>
        private static List<int> GetContactIdListByName(int app_id, string firstname, string middlename, string surname)
        {
            List<int> contactIdList = new List<int>();
            string query = " SELECT ContactId from crm_contact Where deletedFlag=0";
            var sbSql = new StringBuilder(query);
            var searchByNameCondition = new StringBuilder(" and ").AppendFormat("FirstName= '{0}'", firstname)
                                                                  .AppendFormat(" and SurName= '{0}'", surname);

            if (!string.IsNullOrEmpty(middlename))
            {
                searchByNameCondition.AppendFormat(" and MiddleName= '{0}'", middlename);
            }

            sbSql.Append(searchByNameCondition);
            DataTable tb = DBhelper.ExecuteDataset(sbSql.ToString()).Tables[0];
            int tempContactId = 0;

            if (tb.Rows.Count > 0)
            {
                for (int i = 0; i < tb.Rows.Count; i++)
                {
                    if (int.TryParse(tb.Rows[i]["ContactId"].ToString(), out tempContactId))
                    {
                        if (ApplicationDetailHandler.isValidContact(app_id, tempContactId) == 1)
                        {
                            contactIdList.Add(tempContactId);
                        }
                    }
                }
            }
            return contactIdList;
        }
        public static DataSet GetApplicationForClient(int contactID)
        {
            DataSet ds;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@ContactID", contactID),
                };

                string query = "Select crm_applicationcontact.app_id from crm_applicationcontact inner join crm_contact c on c.ContactID=crm_applicationcontact.ContactID where crm_applicationcontact.deletedFlag=0 AND (c.ContactID=@ContactID OR c.DupwithContact=@ContactID)";
                ds = DBhelper.ExecuteDataset(query, para);
            }
            catch (Exception EX)
            {
                throw EX;
            }
            return ds;
        }
        public static bool CheckContactID(int ContactID)
        {
            bool flag = false;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@ContactID", ContactID),
                };

                string query = "Select * from crm_contact where ContactID=@ContactID AND deletedFlag=0";
                DataSet ds = DBhelper.ExecuteDataset(query, para);

                if (ds.Tables[0].Rows.Count > 0)
                    flag = true;
            }
            catch (Exception EX)
            {
                throw EX;
            }
            return flag;
        }

        public static bool CheckForDuplicate(string FirstName, string SurName, string DateBirth)
        {
            bool flag = false;
            try
            {
                if (!string.IsNullOrEmpty(FirstName))
                    FirstName = FirstName.Substring(0, 1) + "%";

                string[] strdate = DateBirth.Split('/');
                DateBirth = string.Concat(strdate[2], "-", strdate[1], "-", strdate[0]);

                var para = new[]{
                    DBhelper.SetParameter("@p_firstName",FirstName),
                    DBhelper.SetParameter("@p_surName",SurName),
                    DBhelper.SetParameter("@p_DateBirth",DateBirth)
                };
                string query = "Select count(*) as avail from crm_contact where deletedFlag=0 AND (DupwithContact IS NULL OR DupwithContact=0) AND (FirstName like @p_firstName  AND SurName=@p_surName  AND DateBirth=@p_DateBirth )";
                Int32 cnt = Convert.ToInt32(DBhelper.ExecuteScalar(query, para));

                if (cnt > 1)
                    flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }
        public static DataSet GetApplicationContactForm(int ContactID)
        {
            Int32 combineId = GetCombineApplicationId(ContactID);

            DataSet ds;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@p_ContactID", ContactID),
                };
                var strQuery = new StringBuilder("SELECT  CONCAT(c.FirstName,' ',c.MiddleName,' ',c.SurName) AS ClientName,udf_getformatedDate(a.CreatedDate) AS cdate,af.`AppFormId` AS FormID ,Assets ,a.CreatedDate AS Age ,a.DesiredAmount,a.PurposeSummary AS LoanPurpose ,IFNULL(a.comesfrom,'') AS comesfrom FROM  crm_contact c ")
                             .Append(" INNER JOIN  crm_applicationcontact ac ON ac.ContactID=c.ContactID ");
                if(combineId > 0)
                {
                    strQuery.Append(" INNER JOIN crm_application a ON a.app_id=ac.CombineApp");
                }
                else
                {
                    strQuery.Append(" INNER JOIN crm_application a ON  a.app_id=ac.app_id");
                }

                strQuery.Append(" INNER JOIN crm_applicationappform af ON af.ApplicationId=a.app_id ")
                        .Append(" WHERE ac.deletedFlag=0 AND c.deletedFlag=0  AND (c.ContactID=@p_ContactID OR  c.DupwithContact=@p_ContactID)");
               
                if (combineId == 0)
                {
                    strQuery.Append(" and a.deletedFlag=0");
                }
               
                ds = DBhelper.ExecuteDataset(strQuery.ToString(), para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        private static Int32 GetCombineApplicationId(int ContactID)
        {
            Int32 combineId = 0;
            DataSet ds;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@p_ContactID", ContactID),
                };
                var strQuery = new StringBuilder("SELECT ac.CombineApp as CombineId FROM  crm_contact c ")
                             .Append(" INNER JOIN  crm_applicationcontact ac ON ac.ContactID=c.ContactID ")
                             .Append(" INNER JOIN crm_application a ON a.app_id=ac.app_id")
                             .Append(" INNER JOIN crm_applicationappform af ON af.ApplicationId=a.app_id ")
                             .Append(" WHERE ac.deletedFlag=0 AND c.deletedFlag=0 and a.deletedFlag=0 AND (c.ContactID=@p_ContactID OR  c.DupwithContact=@p_ContactID)");
                ds = DBhelper.ExecuteDataset(strQuery.ToString(), para);
                Int32.TryParse(ds.Tables[0].Rows[0]["CombineId"].ToString(),out combineId );
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return combineId;
        }

        public static int PartiesContactAdd(int app_id, string Title, string FirstName, string MiddleName, string SurName, string Role, string Relationship, string DateBirth, string MobilePhone, string HomePhone, string WorkPhone, string FaxNumber, string secondEmailAddress, string Email2, string Street, string Suburb, string State, string PostCode, string PostalAddress, string IncomeType, string TypeofEntity, string BusinessName, string ABN, string NatureofBusiness, string Phone, string Address, string Employer, string Occupation, string JobPosition, string EmployerPhone, string ContactPerson, string WorkAddress, string KinsName, string KinsRelationship, string ContactNumber, string Notes)
        {
            int intReturnValue = 0;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("p_AppID", app_id),
                    DBhelper.SetParameter("p_Title", Title),
                    DBhelper.SetParameter("p_FirstName", FirstName),
                    DBhelper.SetParameter("p_MiddleName", MiddleName),
                    DBhelper.SetParameter("p_SurName", SurName),
                    DBhelper.SetParameter("p_Role", Role),
                    DBhelper.SetParameter("p_Relationship", Relationship),
                    DBhelper.SetParameter("p_DateBirth", DateBirth),
                    DBhelper.SetParameter("p_MobilePhone", MobilePhone),
                    DBhelper.SetParameter("p_HomePhone", HomePhone),
                    DBhelper.SetParameter("p_WorkPhone", WorkPhone),
                    DBhelper.SetParameter("p_FaxNumber", FaxNumber),
                    DBhelper.SetParameter("p_Email1", secondEmailAddress),
                    DBhelper.SetParameter("p_Email2", Email2),
                    DBhelper.SetParameter("p_Street", Street),
                    DBhelper.SetParameter("p_Suburb", Suburb),
                    DBhelper.SetParameter("p_State", State),
                    DBhelper.SetParameter("p_PostCode", PostCode),
                    DBhelper.SetParameter("p_PostalAddress", PostalAddress),
                    DBhelper.SetParameter("p_IncomeType", IncomeType),
                    DBhelper.SetParameter("p_TypeofEntity", TypeofEntity),
                    DBhelper.SetParameter("p_BusinessName", BusinessName),
                    DBhelper.SetParameter("p_ABN", ABN),
                    DBhelper.SetParameter("p_NatureofBusiness", NatureofBusiness),
                    DBhelper.SetParameter("p_Phone", Phone),
                    DBhelper.SetParameter("p_Address", Address),
                    DBhelper.SetParameter("p_Employer", Employer),
                    DBhelper.SetParameter("p_Occupation", Occupation),
                    DBhelper.SetParameter("p_JobPosition", JobPosition),
                    DBhelper.SetParameter("p_EmployerPhone", EmployerPhone),
                    DBhelper.SetParameter("p_ContactPerson", ContactPerson),
                    DBhelper.SetParameter("p_WorkAddress", WorkAddress),
                    DBhelper.SetParameter("p_KinsName", KinsName),
                    DBhelper.SetParameter("p_KinsRelationship", KinsRelationship),
                    DBhelper.SetParameter("p_ContactNumber", ContactNumber),
                    DBhelper.SetParameter("p_Notes", Notes)
                };

                DBhelper.ExecuteNonQuerySP("sp_addContactPartiesHistory", para);
                intReturnValue = 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intReturnValue;
        }

        public static int PartiesContactEdit(int ContactID, string Title, string FirstName, string MiddleName, string SurName, string Role, string Relationship, string DateBirth, string MobilePhone, string HomePhone, string WorkPhone, string FaxNumber, string secondEmailAddress, string Email2, string Street, string Suburb, string State, string PostCode, string PostalAddress, string IncomeType, string TypeofEntity, string BusinessName, string ABN, string NatureofBusiness, string Phone, string Address, string Employer, string Occupation, string JobPosition, string EmployerPhone, string ContactPerson, string WorkAddress, string KinsName, string KinsRelationship, string ContactNumber, string Notes)
        {
            int intReturnValue = 0;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@p_ContactID", ContactID),
                    DBhelper.SetParameter("@p_Title", Title),
                    DBhelper.SetParameter("@p_FirstName", FirstName),
                    DBhelper.SetParameter("@p_MiddleName", MiddleName),
                    DBhelper.SetParameter("@p_SurName", SurName),
                    DBhelper.SetParameter("@p_Role", Role),
                    DBhelper.SetParameter("@p_Relationship", Relationship),
                    DBhelper.SetParameter("@p_DateBirth", DateBirth),
                    DBhelper.SetParameter("@p_MobilePhone", MobilePhone),
                    DBhelper.SetParameter("@p_HomePhone", HomePhone),
                    DBhelper.SetParameter("@p_WorkPhone", WorkPhone),
                    DBhelper.SetParameter("@p_FaxNumber", FaxNumber),
                    DBhelper.SetParameter("@p_Email1", secondEmailAddress),
                    DBhelper.SetParameter("@p_Email2", Email2),
                    DBhelper.SetParameter("@p_Street", Street),
                    DBhelper.SetParameter("@p_Suburb", Suburb),
                    DBhelper.SetParameter("@p_State", State),
                    DBhelper.SetParameter("@p_PostCode", PostCode),
                    DBhelper.SetParameter("@p_PostalAddress", PostalAddress),
                    DBhelper.SetParameter("@p_IncomeType", IncomeType),
                    DBhelper.SetParameter("@p_TypeofEntity", TypeofEntity),
                    DBhelper.SetParameter("@p_BusinessName", BusinessName),
                    DBhelper.SetParameter("@p_ABN", ABN),
                    DBhelper.SetParameter("@p_NatureofBusiness", NatureofBusiness),
                    DBhelper.SetParameter("@p_Phone", Phone),
                    DBhelper.SetParameter("@p_Address", Address),
                    DBhelper.SetParameter("@p_Employer", Employer),
                    DBhelper.SetParameter("@p_Occupation", Occupation),
                    DBhelper.SetParameter("@p_JobPosition", JobPosition),
                    DBhelper.SetParameter("@p_EmployerPhone", EmployerPhone),
                    DBhelper.SetParameter("@p_ContactPerson", ContactPerson),
                    DBhelper.SetParameter("@p_WorkAddress", WorkAddress),
                    DBhelper.SetParameter("@p_KinsName", KinsName),
                    DBhelper.SetParameter("@p_KinsRelationship", KinsRelationship),
                    DBhelper.SetParameter("@p_ContactNumber", ContactNumber),
                    DBhelper.SetParameter("@p_Notes", Notes)
                };

                DBhelper.ExecuteNonQuerySP("sp_updateContactPartiesHistory", para);
                intReturnValue = 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intReturnValue;
        }

        public static bool deleteClient(int ContactID)
        {
            bool flag = false;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@ContactID",ContactID)
                };

                string query = "Update crm_contact SET deletedFlag=1 WHERE ContactID=@ContactID; Update crm_applicationcontact SET deletedFlag=1 WHERE ContactID=@ContactID";
                DBhelper.ExecuteNonQuery(query, para);
                flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        public static int DeleteParties(int ContactID)
        {
            int intReturnValue = 0;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@p_ContactID", ContactID),
                };
                DBhelper.ExecuteNonQuery("Delete from crm_contact where ContactID=@p_ContactID", para);
                intReturnValue = 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intReturnValue;
        }

        //public static int GetTotalParties(int LoanID)
        //{
        //    int count = 0;
        //    try
        //    {
        //        var para = new[] {
        //            DBhelper.SetParameter("@LoanID", LoanID),
        //        };

        //        string query = "select count(*) as totalparties from crm_contact where app_id=(select app_id from am_loan1 where LoanID=@LoanID)";
        //        count = Convert.ToInt32(DBhelper.ExecuteScalar(query, para));
        //    }
        //    catch (Exception ex)
        //    {
        //        throw ex;
        //    }
        //    return count;
        //}

        private static bool IsNumberOnly(string value)
        {
            string validChar = "0123456789 ";
            foreach (char c in value)
            {
                if (!validChar.Contains(c.ToString()))
                    return false;
            }
            return true;
        }

        private static bool IsEmailOnly(string value)
        {
            var regex = new Regex(@"[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?");
            return regex.IsMatch(value);
        }

        //Update more client info
        public static int UpdateMoreClientInfo(int ContactID, string SelfEmpBusinessName, string SelfEmpABN, string SelfEmpNatureofBusiness, string SelfEmpPhone, string SelfEmpAddress, string KinsName, string KinsRelationship, string KinsContactNumber, string EmpEmployer, string EmpOccupation, string EmpJobPosition, string EmpEmployerPhone, string EmpContactPerson, string EmpWorkAddress)
        {
            int intReturnValue = 0;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@ContactID", ContactID),
                    DBhelper.SetParameter("@SelfEmpBusinessName", SelfEmpBusinessName),
                    DBhelper.SetParameter("@SelfEmpABN", SelfEmpABN),
                    DBhelper.SetParameter("@SelfEmpNatureofBusiness", SelfEmpNatureofBusiness),
                    DBhelper.SetParameter("@SelfEmpPhone", SelfEmpPhone),
                    DBhelper.SetParameter("@SelfEmpAddress", SelfEmpAddress),
                    DBhelper.SetParameter("@KinsName", KinsName),
                    DBhelper.SetParameter("@KinsRelationship",KinsRelationship),
                    DBhelper.SetParameter("@KinsContactNumber",KinsContactNumber),
                    DBhelper.SetParameter("@EmpEmployer",EmpEmployer),
                    DBhelper.SetParameter("@EmpOccupation",EmpOccupation),
                    DBhelper.SetParameter("@EmpJobPosition",EmpJobPosition),
                    DBhelper.SetParameter("@EmpEmployerPhone",EmpEmployerPhone),
                    DBhelper.SetParameter("@EmpContactPerson",EmpContactPerson),
                    DBhelper.SetParameter("@EmpWorkAddress",EmpWorkAddress),       
                };

                string sbSql = "update crm_contact set SelfEmpBusinessName =@SelfEmpBusinessName,SelfEmpABN=@SelfEmpABN,SelfEmpNatureofBusiness=@SelfEmpNatureofBusiness,SelfEmpPhone=@SelfEmpPhone,SelfEmpAddress=@SelfEmpAddress,KinsName=@KinsName,KinsRelationship=@KinsRelationship,KinsContactNumber=@KinsContactNumber,EmpEmployer=@EmpEmployer,EmpOccupation=@EmpOccupation,EmpJobPosition=@EmpJobPosition,EmpEmployerPhone=@EmpEmployerPhone,EmpContactPerson=@EmpContactPerson,EmpWorkAddress=@EmpWorkAddress where ContactID = @ContactID";
                DBhelper.ExecuteNonQuery(sbSql, para);
                intReturnValue = 1;

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intReturnValue;
        }

        public static DataTable BindContactForEmailSMS(int app_id, int contact_id)
        {
            string str = string.Empty;
            StringBuilder sql = new StringBuilder();
            DataTable dt = new DataTable();
            var para = new[] {
                    DBhelper.SetParameter("@p_ContactID", contact_id),
                    DBhelper.SetParameter("@p_app_id", app_id),
                };

            if (app_id == 0 && contact_id != 0)
                sql = new StringBuilder("SELECT CONCAT(FirstName,' ',SurName) AS FullName,Email,replace(Mobile,' ','') as Mobile FROM crm_contact WHERE ContactID=@p_ContactID and deletedFlag=0");
            else
                sql = new StringBuilder("SELECT CONCAT(crm_contact.FirstName,' ',crm_contact.SurName) AS FullName,crm_contact.Email,replace(crm_contact.Mobile,' ','') as Mobile  FROM crm_applicationcontact")
                    .Append(" INNER JOIN crm_contact ON crm_applicationcontact.ContactID = crm_contact.ContactID WHERE crm_applicationcontact.app_id=@p_app_id AND crm_applicationcontact.deletedFlag=0 AND crm_applicationcontact.NoInApplication=1");

            dt = DBhelper.ExecuteDataset(sql.ToString(), para).Tables[0];


            return dt;
        }

    }
}
